#!/usr/bin/python3
# -*- coding: UTF-8 -*-

from openpyxl import load_workbook, Workbook
import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import NamedStyle, Font, Border, Side, Alignment
import datetime
import argparse

AREAS = ['澄江市', '江川区', '通海县']
LAKES = ['抚仙湖', '星云湖', '杞麓湖']

# G 中央预算内 zhongyang
# H 前期费 qianqi
# I 基建 jijian
# J 专债 zhuanzhai
COLUMNS = ['G', 'H', 'I', 'J']
COLUMNS_NAME = ['中央预算内投资', '省预算内前期工作经费', '省预算内基本建设投资', '地方政府专项债券']

TEM_FILE = 'tem.xlsx'


def get_ts():
    '''获取时间戳'''
    dt = datetime.datetime.now()
    return dt.strftime('%Y年%-m月%-d日')

# 设置两种样式
def get_style_normal():
    style = NamedStyle(name='normal')
    bd = Side(border_style='thin', color="000000")
    style.border = Border(left=bd, top=bd, right=bd, bottom=bd)
    style.font = Font(name='方正仿宋_GBK')
    style.alignment = Alignment(
        wrap_text=True, vertical='center', shrink_to_fit=True)
    return style


def get_style_head():
    style = NamedStyle(name='head')
    bd = Side(border_style='thin', color="000000")
    style.border = Border(left=bd, top=bd, right=bd, bottom=bd)
    style.font = Font(name='方正仿宋_GBK', bold=True)
    style.alignment = Alignment(
        wrap_text=True, vertical='center', shrink_to_fit=True)
    return style


class ThreeLakeReport():
    '''三湖保护数据报告类'''
    def __init__(self, file_path) -> None:
        self.file_path = file_path
        self.want_df = self.read_sheet()
        self.get_df = self.read_sheet(1, 3)

        for col in COLUMNS:
            self.get_df[col] = self.get_df[col].fillna(0)
            self.get_df[col] = self.get_df[col].astype(int)

    def read_sheet(self, sheet_num=0, start_row=2):
        '''读取表'''
        wb = load_workbook(self.file_path, data_only=True)
        ws = wb.worksheets[sheet_num]
        df = pd.DataFrame(list(ws.values)[start_row:])
        df.columns = list(ws.values)[1]
        return df

    def write_txt(self, lines, target_file_path):
        '''输出文本'''
        file = open(target_file_path, 'w')
        file.write('\n'.join(lines))

    def write_book(self, rows, target_file_path):
        '''输出表格'''
        wb = Workbook()
        ws = wb.active
        for row in rows:
            ws.append(row)
        wb.save(target_file_path)

    def report_get(self):
        '''生成争取到资金报告'''
        inner_want_df = self.want_df
        inner_get_df = self.get_df

        # 所有争取到的钱加起来放O列
        inner_want_df['O'] = inner_want_df.loc[:,
                                               COLUMNS].apply(lambda x: x.sum(), axis=1)
        inner_get_df['O'] = inner_get_df.loc[:, COLUMNS].apply(
            lambda x: x.sum(), axis=1)

        # 省预算内不统计
        get_without_H_df = inner_get_df[inner_get_df['H'] == 0]

        # # 省预算内统计在内
        # get_without_H_df = inner_get_df

        # 报表行
        data_rows = []

        # 查找对应项目拟申请的数据
        want_without_H_df = inner_want_df[inner_want_df['C'].isin(
            get_without_H_df['C'])]
        # 总投资
        total = get_without_H_df['F'].sum()
        # 项目个数
        count = get_without_H_df['C'].count()
        # 拟申请
        want_money = want_without_H_df['O'].sum()
        # 实际获得
        get_money = get_without_H_df['O'].sum()
        head = ['一', '已明确支持的项目', '', count, total, want_money,
                '', '', '', '', '', '', '', '', '', get_money]

        data_rows.append(head)

        def get_items(inner_df):
            '''某种类型的钱多少万'''
            return [
                f'{COLUMNS_NAME[COLUMNS.index(col)]}{inner_df[col]}万元' for col in COLUMNS if inner_df[col] > 0
            ]

        # 分湖进行统计
        for area in AREAS:
            get_area_df = get_without_H_df[get_without_H_df['B'] == area]
            want_area_df = want_without_H_df[want_without_H_df['C'].isin(
                get_area_df['C'])]

            def get_lake(area):
                '''由县区获取湖名'''
                return LAKES[AREAS.index(area)]

            area_head = ['', get_lake(area), '', get_area_df['C'].count(), get_area_df['F'].sum(),
                         want_area_df['G'].sum(
            ), '', '', '', '', '', '', '', '',
                '', get_area_df['O'].sum()]

            data_rows.append(area_head)
            i = 1
            for _, row in get_area_df.iterrows():
                money_types = [
                    f'{COLUMNS_NAME[COLUMNS.index(col)]}' for col in COLUMNS if row[col] > 0]
                want_row = want_area_df[want_area_df['C'] == row['C']]
                res_row = [i, row['C'], None, row['D'],
                           row['F'], want_row['G'].sum(), "，".join(money_types), '', '', '', '', '', '', '', '', f'已下达{"，".join(get_items(row))}']
                data_rows.append(res_row)
                i += 1

        return data_rows

    def report_stat(self):
        '''生成三湖所有争取到资金情况含各县区'''
        inner_get_df = self.get_df
        inner_get_df['O'] = inner_get_df.loc[:, COLUMNS].apply(
            lambda x: x.sum(), axis=1)
        items = [
            f'{COLUMNS_NAME[COLUMNS.index(col)]}{inner_get_df[col].sum()}万元' for col in COLUMNS if inner_get_df[col].sum() > 0]

        count = inner_get_df['C'].count()
        get_money = inner_get_df['O'].sum()

        lines = []
        sumary = f'截止{get_ts()}，“三湖”共{count}个项目争取到资金{get_money}万元，其中{"，".join(items)}。'
        lines.append(sumary)

        def get_projects(inner_df, col):
            '''获取得钱的项目名称'''
            return f'（{"、".join([x for x in inner_df[inner_df[col] > 0]["C"]])}）' if col != 'H' else ''

        def get_items(inner_df):
            '''获取某种类型的钱多少万元'''
            return [
                f'{COLUMNS_NAME[COLUMNS.index(col)]}{inner_df[col].sum()}万元{get_projects(inner_df, col)}' for col in COLUMNS if inner_df[col].sum() > 0
            ]

        areas = [
            f'{area}争取资金{inner_get_df[inner_get_df["B"]==area]["O"].sum()}万元，其中{"，".join(get_items(inner_get_df[inner_get_df["B"]==area]))}'
            for area in AREAS
        ]
        sumary_areas = '；'.join(areas) + '。'
        lines.append(sumary_areas)

        [print(line) for line in lines]

        return lines

    def build_df_sheet(self, rows, target_file_path):
        '''输出dataframe样式的表格'''
        wb = Workbook()
        ws = wb.active

        for r in rows:
            ws.append(r)

        for cell in ws['A'] + ws[1]:
            cell.style = 'Pandas'

        wb.save(target_file_path)

    def report_sumary(self):
        '''争取资金统计'''
        
        # 如果需要拟争取的，改数据源就行
        # inner_want_df = self.want_df

        inner_get_df = self.get_df

        inner_get_df['O'] = inner_get_df.loc[:, COLUMNS].apply(
            lambda x: x.sum(), axis=1)

        head = ['县（市、区）', '合计', '总投资', '中央预算内投资',
                '省预算内前期工作经费', '省预算内基本建设投资', '地方政府专项债券', '争取资金合计']

        areas_group = inner_get_df.groupby('B')

        sum_row = ['合计', inner_get_df['C'].count(), inner_get_df['F'].sum(), inner_get_df['G'].sum(),
                   inner_get_df['H'].sum(), inner_get_df['I'].sum(), inner_get_df['J'].sum(), inner_get_df['O'].sum()]

        rows = [sum_row]
        for n, g in areas_group:
            row = [n, g['C'].count(), g['F'].sum(), g['G'].sum(),
                   g['H'].sum(), g['I'].sum(), g['J'].sum(), g['O'].sum()]
            rows.append(row)

        stat_df = pd.DataFrame(rows, columns=head)

        print(stat_df)

        rows = dataframe_to_rows(stat_df, index=True, header=True)
        target = f'{get_ts()}_已争取到资金统计表.xlsx'
        self.build_df_sheet(rows, target)

    def report_split(self):
        '''生成需要支持的三个清单文件'''
        inner_want_df = self.want_df

        def get_col_name(area):
            '''根据列获取钱的名称'''
            return COLUMNS_NAME[COLUMNS.index(area)]

        def get_sumary(inner_df, area, col):
            '''获取某个湖的统计'''
            count = inner_df['C'].count()
            return ['', area, f'{count}个', '', '', inner_df['F'].sum(), inner_df[col].sum()]

        def clean_columns(col):
            '''清除某些列'''
            droped_columns = list(COLUMNS)
            droped_columns.remove(col)
            return droped_columns

        def get_rows(inner_df, area, col):
            '''获取填表的行'''
            rows = [get_sumary(inner_df, area, col)]
            filtered_columns = clean_columns(col)
            filtered_df = inner_df.drop(labels=filtered_columns, axis=1)
            for i, r in enumerate(dataframe_to_rows(filtered_df, index=False, header=False)):
                r[0] = i + 1
                r[7] = f'恳请帮助协调申请{get_col_name(col)}{round(r[6])}万元'
                rows.append(r)

            return rows

        def get_col_rows(inner_df, col):
            '''获取某种钱所有行，按湖生成行'''
            rows = [get_sumary(inner_df, '合计', col)]
            for area in AREAS:
                area_df = inner_df[inner_df['B'] == area]
                rows.extend(get_rows(area_df, area, col))
            return rows

        def write_sheet(ws, rows):
            '''把数据填到表中带格式'''
            for r in rows:
                ws.append(r)

            for row in ws.iter_rows(min_row=2):
                for cell in row:
                    cell.style = 'normal'
            for c in ws[2]:
                c.style = 'head'

        def write_template(template_path, target, inner_df):
            '''把三湖需要支持的数据分类填到表中'''
            wb = load_workbook(template_path, data_only=True)
            wb.add_named_style(get_style_normal())
            wb.add_named_style(get_style_head())

            for ind, col in enumerate(COLUMNS):
                rows = get_col_rows(inner_df[inner_df[col] > 0], col)
                write_sheet(wb.worksheets[ind], rows)

            wb.save(target)

        target = f'{get_ts()}_附件.玉溪市“三湖”保护治理需省发展改革委支持事项三张清单.xlsx'
        write_template(TEM_FILE, target, inner_want_df)


def main():

    parser = argparse.ArgumentParser(description='三湖保护治理项目报表统计')

    parser.add_argument('-type', help='报表类型 get|stat|split|sumary')
    parser.add_argument('file')

    args = parser.parse_args()

    print('args', args)
    rep = ThreeLakeReport(args.file)
    if args.type == 'get':
        rows = rep.report_get()
        target = f'{get_ts()}_市发展改革委-附件：“三湖”保护治理项目表.xlsx'
        rep.write_book(rows, target)
    elif args.type == 'stat':
        lines = rep.report_stat()
        target = f'{get_ts()}_“三湖”争取到资金统计.txt'
        rep.write_txt(lines, target)
    elif args.type == 'split':
        rep.report_split()
    elif args.type == 'sumary':
        rep.report_sumary()

if __name__ == '__main__':
    main()

    print('Done')
